package com.ipan.poi.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * JDBC简单操作封装；
 * 注意：executeWithPreparedStatement、executeWithStatement、executeWithConnection不要嵌套，那样事务就不是一个了！
 * 
 * @author iPan
 * @version 2013-8-7
 */
public class JDBCManager {
	private static boolean BEGIN_TRANSACTION = true;
	private static Logger logger = LoggerFactory.getLogger(JDBCManager.class);
	
	/**
	 * 批量执行，PreparedStatement回调；
	 */
	public static void executeWithPreparedStatement(JDBCConfig config, final JDBCPreparedStatementCallback callback, 
			final String sql, boolean beginTransaction) {
		executeWithConnection(config, new JDBCConnectionCallback() {
			public void doInConnection(Connection conn) {
				PreparedStatement statement = null;
				try {
					statement = conn.prepareStatement(sql);
					callback.doInPreparedStatement(statement);
				} catch (SQLException e) {
					throwJDBCException(e);
				} finally {
					closeStatement(statement);
				}
			}
		}, beginTransaction);
	}
	public static void executeWithPreparedStatement(JDBCConfig config, final JDBCPreparedStatementCallback callback, final String sql) {
		executeWithPreparedStatement(config, callback, sql, BEGIN_TRANSACTION);
	}
	
	/**
	 * 批量执行，Statement回调；
	 */
	public static void executeWithStatement(JDBCConfig config, final JDBCStatementCallback callback, boolean beginTransaction) {
		executeWithConnection(config, new JDBCConnectionCallback() {
			public void doInConnection(Connection conn) {
				Statement statement = null;
				try {
					statement = conn.createStatement();
					callback.doInStatement(statement);
				} catch (SQLException e) {
					throwJDBCException(e);
				} finally {
					closeStatement(statement);
				}
			}
		}, beginTransaction);
	}
	public static void executeWithStatement(JDBCConfig config, final JDBCStatementCallback callback) {
		executeWithStatement(config, callback, BEGIN_TRANSACTION);
	}
	
	/**
	 * 在同一个事务里面批量执行，Connection回调；
	 */
	public static void executeWithConnection(JDBCConfig config, JDBCConnectionCallback callback, boolean beginTransaction) {
		Connection conn = createConnection(config);
		try { 
			if (beginTransaction) {
				conn.setAutoCommit(false);
			}
			callback.doInConnection(conn);
			conn.commit();
		} catch(SQLException e) {
			throwJDBCException(e);
		} catch(Throwable e) {
			try {
				conn.rollback();
				if (e instanceof JDBCException) {
					throw (JDBCException) e ;
				} else {
					throw new RuntimeException(e);
				}
			} catch (SQLException rollEx) {
				throwJDBCException(rollEx);
			}
		} finally {
			closeConnection(conn);
		}
	}
	public static void executeWithConnection(JDBCConfig config, JDBCConnectionCallback callback) {
		executeWithConnection(config, callback, BEGIN_TRANSACTION);
	}
	
	/**
	 * 单条SQL查询语句；
	 */
	public static ResultSet ExecuteQuery(PreparedStatement preparedStatement, String sql, Object[] params) {
		ResultSet resultSet = null;
		try {
			fillStatement(preparedStatement, params);
			resultSet = preparedStatement.executeQuery();
		} catch (SQLException e) {
			throwJDBCException(e, sql, params);
		}
		return resultSet;
	}
	
	/**
	 * 查询唯一记录；
	 */
	public static Object queryUnique(Connection conn, String sql, Object[] params) {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Object result = null;
		try {
			preparedStatement = conn.prepareStatement(sql);
			fillStatement(preparedStatement, params);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				result = resultSet.getObject(1);
			}
		} catch (SQLException e) {
			throwJDBCException(e, sql, params);
		} finally {
			closeResultSet(resultSet);
			closeStatement(preparedStatement);
		}
		return result;
	}
	
	/**
	 * 查询唯一记录；
	 */
	public static Object queryUnique(PreparedStatement preparedStatement, String sql, Object[] params) {
		ResultSet resultSet = null;
		Object result = null;
		try {
			fillStatement(preparedStatement, params);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				result = resultSet.getObject(1);
			}
		} catch (SQLException e) {
			throwJDBCException(e, sql, params);
		} finally {
			closeResultSet(resultSet);
		}
		return result;
	}
	
	/**
	 * 记录是否存在，根据count(*)>0查询；
	 */
	public static boolean exists(Connection conn, String sql, Object[] params) {
		if (!sql.toLowerCase().contains("count")) {
			throw new java.lang.IllegalArgumentException("使用count统计记录是否存在时，SQL格式不正确！");
		}
		PreparedStatement preparedStatement = null;
		boolean result = false;
		try {
			preparedStatement = conn.prepareStatement(sql);
			result = exists(preparedStatement, sql, params);
		} catch (SQLException e) {
			throwJDBCException(e, sql, params);
		} catch (JDBCException ex) {
			throw ex;
		} finally {
			closeStatement(preparedStatement);
		}
		return result;
	}
	public static boolean exists(PreparedStatement preparedStatement, String sql, Object[] params) {
		ResultSet resultSet = null;
		boolean result = false;
		try {
			fillStatement(preparedStatement, params);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				int count = resultSet.getInt(1);
				result = (count > 0) ? true : false;
			}
		} catch (SQLException e) {
			throwJDBCException(e, sql, params);
		} finally {
			closeResultSet(resultSet);
		}
		return result;
	}

	/**
	 * 单条SQL更新语句；
	 */
	public static int ExecuteUpdate(Connection connection, String sql, Object[] params) {
		int rows = 0;
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			fillStatement(preparedStatement, params);
			rows = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throwJDBCException(e, sql, params);
		} finally {
			closeStatement(preparedStatement);
		}
		return rows;
	}
	public static int ExecuteUpdate(Connection connection, String sql) {
		return ExecuteUpdate(connection, sql, null);
	}

	/**
	 * 参数填充；
	 */
	public static void fillStatement(PreparedStatement stmt, Object[] params) {
		if (params == null || params.length < 1) {
			return;
		}
		try {
			for (int i = 0; i < params.length; ++i) {
				if (params[i] != null) {
					stmt.setObject(i + 1, params[i]);
				} else {
					stmt.setNull(i + 1, Types.CHAR);
				}
			}
		} catch (SQLException e) {
			throwJDBCException(e);
		}
	}
	
	/**
	 * 从Map中获取字段列表的值；
	 */
	public static Object[] getParams(Map<String, Object> data, List<String> column) {
		if (data == null || column == null || data.size() < 1 || column.size() < 1) {
			return null;
		}
		List<Object> result = new ArrayList<Object>();
		for (String name : column) {
			Object o = data.get(name);
			if (o != null) {
				result.add(o);
			}
		}
		return result.toArray();
	}
	
	/**
	 * 获取链接；
	 */
	public static Connection createConnection(JDBCConfig config) {
		try {
			Class.forName(config.getDriver());
		} catch (ClassNotFoundException e) {
			throw new java.lang.IllegalArgumentException(e);
		}
		
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(config.getUrl(), config.getUserName(), config.getPassWord());
		} catch (SQLException e) {
			throwJDBCException(e);
		}
		return conn;
	}
	
	/**
	 * 关闭Connection；
	 */
	public static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				logger.warn("关闭Connection出错！", e);
			}
		}
	}

	/**
	 * 关闭Statement；
	 */
	public static void closeStatement(Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				logger.warn("关闭Statement出错！", e);
			}
		}
	}

	/**
	 * 关闭ResultSet；
	 */
	public static void closeResultSet(ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				logger.warn("关闭ResultSet出错！", e);
			}
		}
	}
	
	public static void throwJDBCException(Exception e) {
		throw new JDBCException(e);
	}
	
	public static void throwJDBCException(SQLException e, String sql, Object[] params) {
		throw new JDBCException(e, sql, params);
	}
	
	public static void commit(Connection conn) {
		try {
			conn.commit();
		} catch (SQLException e) {
			logger.warn("提交事务出错！", e);
		}
	}
	
	public static void rollback(Connection conn) {
		try {
			conn.rollback();
		} catch (SQLException e) {
			logger.warn("回滚事务出错！", e);
		}
	}
	
}
